
package Project;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author admin
 */
public class DBHelper {
    private static Connection con = null;
    /** Creates a new instance of DBHelper */
    public DBHelper() {
        if (con == null) {
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                con=DriverManager.getConnection("jdbc:odbc:JDBC", "sa", "123");
            } catch(ClassNotFoundException e) {
                e.printStackTrace();
            } catch(SQLException sqle) {
                sqle.printStackTrace();
            }
        }
        
        
    }
    
    public static Connection getConnection()
    {
        return con;
    }
           

    public static ResultSet executeQuery(String spName) throws SQLException {
        if (con != null) {
            CallableStatement stmt = con.prepareCall("{call " + spName + "}");
            return stmt.executeQuery();
        }
       
        return null;
    }
    // method execute query
    public static ResultSet executeQuery(String spName, Vector paramList) throws SQLException {
        if (con != null) {
            String stmt = "{call " + spName + "(";
                   
            int t = 0;


             for(Object obj : paramList) {
                if (t != 0)
                    stmt += ",";
                
                if (obj instanceof Integer) {
                    Integer i = (Integer)obj;
                    stmt += i.toString();
                } else if (obj instanceof String) {
                    String s = (String)obj;
                    stmt += "'" + s + "'";
                }else if (obj instanceof Float ){
                    Float i=(Float)obj;
                    stmt += i.toString();
                }else if (obj instanceof Boolean){
                    Boolean i = (Boolean) obj;
                    stmt += i.toString();
                }
                t++;
            }
             
            stmt += ")}" ;
            
            CallableStatement statement = con.prepareCall(stmt);
            return statement.executeQuery();
        }
        
        return null;
    }
     

    // method to delete one record in table
    public static int executeDelete(String spName, int id) throws SQLException
    {
        if (con!=null)
        {
            String stmt = "{call " + spName + "("+id+")}";  
            CallableStatement statement = con.prepareCall(stmt);
            return statement.executeUpdate();              
        }
        return -1;
    }
    //method to update one record in table
    public static int executeUpdate(String spName, Vector paramList) throws SQLException 
    {
        if (con != null) {
            String stmt = "{call " + spName + "(";                   
            int t = 0;
            for(Object obj : paramList) {
                if (t != 0)
                    stmt += ",";
                
                if (obj instanceof Integer) {
                    Integer i = (Integer)obj;
                    stmt += i.toString();
                } else if (obj instanceof String) {
                    String s = (String)obj;
                    stmt += "'" + s + "'";
                }else if (obj instanceof Float ){
                    Float i=(Float)obj;
                    stmt += i.toString();
                }                
                t++;
            }            
            stmt += ")}" ;            
            CallableStatement statement = con.prepareCall(stmt);
            return statement.executeUpdate();
        }        
        return -1;
    }
    public static int executeSelect(String spName,int id) throws SQLException 
    {
        int k=0;
        if (con != null) {
            String stmt = "{call " + spName + "(" + id + ")}";      
            CallableStatement statement = con.prepareCall(stmt);
            ResultSet r= statement.executeQuery();
            while (r.next())  k=r.getInt(1);
            return k;
        }   
        return -1;
    }
     public static void backUpDatabase(String path) throws SQLException
    {
        if (con != null) {
            String sqlstmt = "{CALL backup_proc(?)}";
            CallableStatement cs = con.prepareCall(sqlstmt);
            cs.setString(1,path);
            cs.execute();
            cs.close();
            }
     }
}
    
